/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Utility;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author TOSHIBA
 */
public class TagUtils implements Serializable {

    public static void updateTag(String tagName, int placeID, boolean typeOfTag) throws SQLException {
        int tagID = 0;
        tagID = checkTagExistInTagTable(tagName);
        if (tagID == 0) {
            tagID = addTagToTagTable(tagName, typeOfTag);
        }
        if (!checkTagExistInTagPlaceTable(tagID, placeID)) {
            addTagToTagPlaceTable(tagID, placeID);
        }

    }
    //return id of tag

    public static int addTagToTagTable(String tag, boolean type) throws SQLException {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;

        con = makeConnection();
        String sql = "Insert Into Tag(Tag_Name, Tag_Type) VALUES(?,?)";
        stm = con.prepareStatement(sql, stm.RETURN_GENERATED_KEYS);

        stm.setString(1, tag);
        stm.setBoolean(2, type);
        stm.executeUpdate();
        rs = stm.getGeneratedKeys();
        if (rs.next()) {
            return rs.getInt(1);
        }
        try {
            if (stm != null) {
                stm.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    public static void addTagToTagPlaceTable(int tagID, int placeID) throws SQLException {
        Connection con = null;
        PreparedStatement stm = null;

        con = makeConnection();
        String sql = "Insert Into Place_Tag(Place_ID, Tag_ID, Point) VALUES(?,?,?)";
        stm = con.prepareStatement(sql);
        stm.setInt(1, placeID);
        stm.setInt(2, tagID);
        stm.setInt(3, 0);
        stm.executeUpdate();

        try {
            if (stm != null) {
                stm.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static int checkTagExistInTagTable(String tag) throws SQLException {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;

        con = DBUtils.makeConnection();
        String sql = "select Tag_ID from Tag where Tag_Name = ?";
        stm = con.prepareStatement(sql);
        stm.setString(1, tag);
        rs = stm.executeQuery();
        if (rs.next()) {
            return rs.getInt(1);
        }

        try {
            if (rs != null) {
                rs.close();
            }
            if (stm != null) {
                stm.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (Exception e) {
            return 0;
        }

        return 0;
    }

    public static boolean checkTagExistInTagPlaceTable(int tagID, int placeID) throws SQLException {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        con = DBUtils.makeConnection();
        String sql = "select Place_Tag_ID from Place_Tag where Tag_ID = ? and Place_ID=?";
        stm = con.prepareStatement(sql);
        stm.setInt(1, tagID);
        stm.setInt(2, placeID);
        rs = stm.executeQuery();
        if (rs.next()) {
            return true;
        }

        try {
            if (rs != null) {
                rs.close();
            }
            if (stm != null) {
                stm.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (Exception e) {
            return false;
        }

        return false;
    }
    public static void deleteTagPlace(int tag_place_id) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = makeConnection();
            String sql = "Delete From Place_Tag Where Place_Tag_ID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, tag_place_id);
            stm.executeUpdate();

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static Connection makeConnection() {
        Connection con = null;
        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://localhost:1433;databaseName=TOURDB;instanceName=CHAULVN60511/MSSQLSERVER2008";
            con = DriverManager.getConnection(url, "sa", "123456");
            return con;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
//        } finally {
//            try {
//                if (con != null) {
//                    con.close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
        }
        return null;
    }

}
